﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace ProdeManagmente.Libs
{
    public class Persistencia
    {
        private static Persistencia instance;
        private String cadena;
        private SqlCommand command;
        private SqlConnection con;
        private SqlTransaction transaction;

        public static Persistencia getInstance()
        {
            if (instance == null)
            {
                instance = new Persistencia("Prode");
            }
            return instance;
        }

        private Persistencia(string nCadena )
        {
            try
            {                
                appConfig(nCadena);
                command = con.CreateCommand();
                command.Connection = con;
            }
            catch (BusinessException e)
            {
                throw e;
            }
        }

        public Persistencia()
        { }

        private void appConfig(string nCadena)
        {
            try
            {
                this.cadena = ConfigurationManager.ConnectionStrings[nCadena].ConnectionString;
                this.con = new SqlConnection();
                this.con.ConnectionString = this.cadena;
                this.con.Open();
            }
            catch (NullReferenceException eNull)
            {
                throw new BusinessException("202", "No se encontro cadena de conexion para: " + nCadena);
            }
            catch (SqlException eSql)
            {
                throw new BusinessException("201", eSql.Message);
            }
        }

        public DataTable executeQuery(String sql)
        {
            try
            {

                SqlDataAdapter sda = new SqlDataAdapter(sql, this.con);
                DataSet ds = new DataSet("NuevoDataSet");
                sda.Fill(ds, "Tabla_1");
                DataTable tabla = ds.Tables[0];
                return tabla;
            }
            catch (Exception e)
            {
                throw new BusinessException("203", "Error executeQuery: " + e.Message);
            }
        }
        public String executeQueryXML(String sql)
        {
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, this.con);
                DataSet ds = new DataSet("NuevoDataSet");
                sda.Fill(ds, "Tabla_1");
                return ds.GetXml();
            }
            catch (Exception e)
            {
                throw new BusinessException("204", "Error executeQueryXML: " + e.Message);
            }
        }

        public String ExecuteNonQuery(String sql)
        {
            try
            {
                string filasAfectadas = "0";
                this.command.CommandText = sql;
                filasAfectadas = this.command.ExecuteNonQuery() + "";
                return filasAfectadas;
            }
            catch (Exception e)
            {
                throw new BusinessException("205", "Error ExecuteNonQuery: " + e.Message);
            }
        }

        public void abrirTransaccion()
        {
            this.transaction = con.BeginTransaction();
            this.command.Transaction = transaction;
        }
        public void rolback()
        {
            this.transaction.Rollback();
        }

        public void commit()
        {
            this.transaction.Commit();
        }

        public void cerrarConexion()
        {
            this.con.Close();
        }
    }
}
